/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Golf_Manager.sqlite.sql;

import Golf_Manager.vo.Group;

/**
 *
 * @author Administrator
 */
public class GroupSql {
    
    public static StringBuffer selectGroupJoinMemberCountSql(String groupDate){
        
        StringBuffer selectGroupJoinMemberCountSql = new StringBuffer();
        
        selectGroupJoinMemberCountSql.append("SELECT");
        selectGroupJoinMemberCountSql.append(" CASE WHEN COUNT(GDATE) > 0 THEN 'Y' ELSE 'N' END GROUP_EXIST ");
        selectGroupJoinMemberCountSql.append("FROM GROUP_MST ");
        selectGroupJoinMemberCountSql.append("WHERE GDATE = '").append(groupDate).append("'");
        
        //System.out.println(selectGroupJoinMemberSql.toString());
        
        return selectGroupJoinMemberCountSql;
    }
    
    public static StringBuffer selectGroupMstSql(String groupDate){
        
        StringBuffer selectGroupMstSql = new StringBuffer();
        
        selectGroupMstSql.append("SELECT");
        selectGroupMstSql.append(" GDATE, ");
        selectGroupMstSql.append(" GKEY, ");
        selectGroupMstSql.append(" GORDER, ");
        selectGroupMstSql.append(" GRECYN ");
        selectGroupMstSql.append("FROM GROUP_MST ");
        selectGroupMstSql.append("WHERE GDATE = '").append(groupDate).append("'");
        
        //System.out.println(selectGroupJoinMemberSql.toString());
        
        return selectGroupMstSql;
    }
    
    public static StringBuffer selectGroupMstBetweenSql(String groupDateFrom, String groupDateTo){
        
        StringBuffer selectGroupMstListSql = new StringBuffer();
        
        selectGroupMstListSql.append("SELECT");
        selectGroupMstListSql.append(" GDATE, ");
        selectGroupMstListSql.append(" GKEY, ");
        selectGroupMstListSql.append(" GORDER, ");
        selectGroupMstListSql.append(" GRECYN ");
        selectGroupMstListSql.append("FROM GROUP_MST ");
        selectGroupMstListSql.append("WHERE GDATE BETWEEN '").append(groupDateFrom).append("' AND '").append(groupDateTo).append("'");
        
        //System.out.println(selectGroupMstListSql.toString());
        
        return selectGroupMstListSql;
    }
    
    public static StringBuffer selectGroupDtlSql(String groupDate){
        
        StringBuffer selectGroupDtlSql = new StringBuffer();
        
        selectGroupDtlSql.append("SELECT");
        selectGroupDtlSql.append(" GDATE, ");
        selectGroupDtlSql.append(" GSEQ, ");
        selectGroupDtlSql.append(" GNAME, ");
        selectGroupDtlSql.append(" GMCNT ");
        selectGroupDtlSql.append("FROM GROUP_DTL ");
        selectGroupDtlSql.append("WHERE GDATE = '").append(groupDate).append("'");
        
        //System.out.println(selectGroupDtlSql.toString());
        
        return selectGroupDtlSql;
    }
    
    public static StringBuffer selectGroupJoinMembersSql(String groupDate){
        
        StringBuffer selectGroupJoinMembersSql = new StringBuffer();
        
        selectGroupJoinMembersSql.append("SELECT");
        selectGroupJoinMembersSql.append(" GDATE, ");
        selectGroupJoinMembersSql.append(" GROW, ");
        selectGroupJoinMembersSql.append(" GCOL, ");
        selectGroupJoinMembersSql.append(" GNAME, ");
        selectGroupJoinMembersSql.append(" MSEQ, ");
        selectGroupJoinMembersSql.append(" MNAME, ");
        selectGroupJoinMembersSql.append(" MBIRTH, ");
        selectGroupJoinMembersSql.append(" MSCORE ");
        selectGroupJoinMembersSql.append("FROM JOINMEMBERS ");
        selectGroupJoinMembersSql.append("WHERE GDATE = '").append(groupDate).append("' ORDER BY GROW, GCOL");
        
        //System.out.println(selectGroupJoinMembersSql.toString());
        
        return selectGroupJoinMembersSql;
    }
    
    public static StringBuffer getInsterGroupMstSql(String groupDate, String selectKey, String order, Boolean recordCheck) {
        
        StringBuffer insertGroupMstSql = new StringBuffer();
        
        insertGroupMstSql.append("INSERT INTO GROUP_MST (GDATE,  GKEY,  GORDER,  GRECYN) ");
        insertGroupMstSql.append("VALUES ( '" ).append(groupDate).append("', ");
        insertGroupMstSql.append("'").append(selectKey).append("', ");
        insertGroupMstSql.append("'").append(order).append("', ");
        insertGroupMstSql.append("'").append(recordCheck == true ? "Y" : "N").append("')");
        
        //System.out.println(insertGroupMstSql.toString());
        
        return insertGroupMstSql;
    }
    
    public static StringBuffer getDeleteGroupMstSql(String groupDate) {
        
        StringBuffer deleteGroupMstSql = new StringBuffer();
        
        deleteGroupMstSql.append("DELETE FROM GROUP_MST ");
        deleteGroupMstSql.append("WHERE GDATE IN ('" ).append(groupDate).append("')");
        
        //System.out.println(deleteGroupMstSql.toString());
        
        return deleteGroupMstSql;
    }
    
    public static StringBuffer getInsterGroupDtlSql(String groupDate, Group group) {
        
        StringBuffer insertGroupDtlSql = new StringBuffer();
        
        insertGroupDtlSql.append("INSERT INTO GROUP_DTL (GDATE,  GSEQ,  GNAME,  GMCNT) ");
        insertGroupDtlSql.append("VALUES ( '" ).append(groupDate).append("', ");
        insertGroupDtlSql.append(group.getGroupRow()).append(", ");
        insertGroupDtlSql.append("'").append(group.getGroupName()).append("', ");
        insertGroupDtlSql.append("'").append(group.getGroupMemberCnt()).append("')");
         
        //System.out.println(insertGroupDtlSql.toString());
        
        return insertGroupDtlSql;
    }
    
    public static StringBuffer getDeleteGroupDtlSql(String groupDate) {
        
        StringBuffer deleteGroupDtlSql = new StringBuffer();
        
        deleteGroupDtlSql.append("DELETE FROM GROUP_DTL ");
        deleteGroupDtlSql.append("WHERE GDATE IN ('" ).append(groupDate).append("')");
        
        //System.out.println(deleteGroupDtlSql.toString());
        
        return deleteGroupDtlSql;
    }
    
    public static StringBuffer getCompJoinMembrsSql(String origin, String target) {
        
        StringBuffer compJoinMembrsSql = new StringBuffer();
        
        compJoinMembrsSql.append("SELECT  SUM(CNT) COMP_VAL FROM (");
        compJoinMembrsSql.append("SELECT ").append(origin).append(" AS PLAYER, ").append(target).append(" AS COMP , 0 CNT ");
        compJoinMembrsSql.append("UNION ALL ");
        compJoinMembrsSql.append("SELECT A.MSEQ PLAYER , B.MSEQ COMP , COUNT(*) CNT ");
        compJoinMembrsSql.append("FROM JOINMEMBERS A,  JOINMEMBERS B ");
        compJoinMembrsSql.append("WHERE A.GDATE = B.GDATE ");
        compJoinMembrsSql.append("AND A.GROW = B.GROW ");
        compJoinMembrsSql.append("AND SUBSTR(A.GDATE, 0,5) = STRFTIME('%Y', 'NOW', 'LOCALTIME') ");
        compJoinMembrsSql.append("AND A.MSEQ <> B.MSEQ ");
        compJoinMembrsSql.append("AND A.MSEQ = ").append(origin).append(" ");
        compJoinMembrsSql.append("AND B.MSEQ = ").append(target).append(" ");
        compJoinMembrsSql.append("GROUP BY A.MSEQ ,B.MSEQ) ");
        
        System.out.println(compJoinMembrsSql.toString());
        
        return compJoinMembrsSql;
    }
}
